﻿using Mode;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    /// <summary>
    /// (数据访问层)
    /// </summary>
    public class BookInfoDAL
    {
        SqlCommand cmd;//定义SQLcommand对象
        public BookInfoDAL()
        {
            cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;//使用存储过程访问数据库
        }
        public DataSet GetBookClass()
        {
            cmd.CommandText = "GetBookClass";//指定存储过程名称
            cmd.Parameters.Clear();//清除存储过程参数值

            DataSet ds = DBHelper.GetDataSet(cmd);
            return ds;
        }
        //数据库查重
        public bool CheckingDulication(Bookinfo bo)
        {
            cmd.CommandText = "GetBookByBookID";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@BookID", SqlDbType.Char, 15).Value = bo.bookID;
            object obj = DBHelper.GerScalar(cmd);
            if (obj !=null )
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public bool EditBook(Bookinfo bo, string operation)
        {
            if (operation == "I")
                cmd.CommandText = "InsertNewBook"; //当操作值为"I"，进行新增操作
            if (operation == "U")
                cmd.CommandText = "UpdateBook"; //当操作值为"U"，进行更新操作
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@BookID", SqlDbType.VarChar, 15).Value = bo.bookID ;
            cmd.Parameters.Add("@BookName", SqlDbType.VarChar, 200).Value = bo.bookName ;
            cmd.Parameters.Add("@ISBN", SqlDbType.Char, 20).Value = bo.ISBN;
            cmd.Parameters.Add("@Author", SqlDbType.VarChar, 50).Value = bo.author ;
            cmd.Parameters.Add("@Publisher", SqlDbType.VarChar, 40).Value = bo.publisher ;
            cmd.Parameters.Add("@BookVersion", SqlDbType.VarChar, 40).Value = bo.bookVersion ;

            cmd.Parameters.Add("@WordCount", SqlDbType.Int).Value = bo.wordCount ;
            cmd.Parameters.Add("@PageCount", SqlDbType.Int).Value = bo.pageCount ;

            cmd.Parameters.Add("@PublishDate", SqlDbType.DateTime).Value = bo.publishDate ;

            cmd.Parameters.Add("@ClassID", SqlDbType.Char, 10).Value = bo.Bookclass.classId ;


            try
            {
                int num = DBHelper.ExecuteSQL(cmd);
                if (num > 0)
                {
                    return true;
                }
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 读取图书类别数据源
        /// </summary>
        /// <param name="reader">数据库读取对象</param>
        /// <returns>图书类别对象：BookClass</returns>
        private static Bookclass GetBookCalssByDataReader(SqlDataReader reader)
        {
            string bookClassID = reader["classId"].ToString().Trim();
            string bookClassName = reader["className"].ToString().Trim();
            Bookclass obj = new Bookclass(bookClassID, bookClassName);
            return obj;
        }
        /// <summary>
        /// 获取图书信息
        /// </summary>
        /// <param name="bookClassID">图书类别编号值</param>
        /// <param name="bookClassName">图书类别名称</param>
        /// <returns>图书类别对象：BookClass</returns>
        public Bookclass FindBookClass(string bookClassID = null, string bookClassName = null)
        {
            Bookclass obj = new Bookclass();
            if (bookClassID == null)
                obj.classId  = "";
            else obj.classId  = bookClassID;
            if (bookClassName == null)
                obj.classname  = "";
            else obj.classname  = bookClassName;
            cmd.CommandText = "GetBookClassByBookClass";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@bookClassID", SqlDbType.VarChar, 50).Value = obj.classId ;
            cmd.Parameters.Add("@bookClassName", SqlDbType.VarChar, 50).Value = obj.classname ;
            SqlDataReader reader = DBHelper.ExcuteReader(cmd);
            while (reader.Read())
            {
                Bookclass o = GetBookCalssByDataReader(reader);
                reader.Close();
                return o;
            }
            reader.Close();
            return null;
        }

        /// <summary>
        /// 获取图书信息
        /// </summary>
        /// <param name="bookname">图书名称</param>
        /// <param name="bookClassName">图书类别名称</param>
        /// <returns>图书类别对象：BookClass</returns>

        public DataSet GetBookByBookNameOrClassname(string bookname,string classname)
        {
            string classid = "";
            Bookclass bo = new Bookclass();
            bo.classname   = classname;
            if (bo.classname .Length > 0)
            {
          
                classid = FindBookClass(bo.classId , bo.classname ).classId ;
            }
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetBookInfoBySearch";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@bookName", SqlDbType.NVarChar, 200).Value = bookname;
            cmd.Parameters.Add("@bookClassid", SqlDbType.VarChar, 10).Value = classid;

            DataSet ds = DBHelper.GetDataSet(cmd);
            return ds;
        }

        /// <summary>
        /// 根据索书号获取相关记录
        /// </summary>
        /// <param name="bookid">索书号</param>
        /// <returns>数据集</returns>

        public DataSet GetBookByBookId(string bookid)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetBookByBookID";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@BookID", SqlDbType.VarChar, 15).Value = bookid;
            DataSet ds = DBHelper.GetDataSet(cmd);
            return ds;
        }
        public bool DeleteBook(string bookid)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "DeleteBookByBookID";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@BookID", SqlDbType.VarChar, 15).Value = bookid;

            var returnVal = cmd.Parameters.Add("@returnValue", SqlDbType.Int);//定义存储过程输出参数output
            returnVal.Direction = ParameterDirection.Output;                   //说明output为存储过程输出参数
            try
            {
                int num = DBHelper.ExecuteSQL(cmd);
                int val = Convert.ToInt16(returnVal.Value);
                if (num > 0 || val == 1)
                {
                    return true;
                }
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataSet  GetBorrowBookByUserID(string userid)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetBorrowInfoByUserID";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@userid", SqlDbType.VarChar, 11).Value = userid ;
            DataSet ds = DBHelper.GetDataSet(cmd);
            return ds;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="bookid"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public object InsertBorrowInfo(string bookid,string userid)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetBorrowInfoByUserID";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@BookID", SqlDbType.VarChar, 11).Value =bookid ;
            cmd.Parameters.Add("@UserID", SqlDbType.VarChar, 11).Value = userid;
            var returnVal = cmd.Parameters.Add("@resultVal", SqlDbType.Int);//定义存储过程输出参数output
            returnVal.Direction = ParameterDirection.Output;                   //说明output为存储过程输出参数
            try
            {
                int num = DBHelper.ExecuteSQL(cmd);
                if (num > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }     

        }
        public bool BookSateByBookID(string bookid)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetBookStateByBookID";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@BookID", SqlDbType.VarChar, 11).Value =bookid ;
            var returnVal = cmd.Parameters.Add("@returnValue", SqlDbType.Int);
            returnVal.Direction = ParameterDirection.Output;
            try
            {
                object state = DBHelper.ExcuteReader(cmd).ToString();
                var output = returnVal.Value.ToString();
                return Convert.ToBoolean(output);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 还书管理，图书/读者/借阅信息读取
        /// </summary>
        /// <param name="userid">读者编号</param>
        /// <param name="bookid">索书号</param>
        /// <returns>DataSet数据集</returns>
        public DataSet GetBorrowInfo(string userid, string bookid)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetBorrowInfo";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@bookid", SqlDbType.VarChar, 20).Value =bookid ;
            cmd.Parameters.Add("@userid", SqlDbType.VarChar, 11).Value =userid ;


            DataSet ds = DBHelper.GetDataSet(cmd);
            return ds;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="bookid"></param>
        /// <returns></returns>
        public bool ReturnBook(string bookid)
        {
            cmd.CommandType = CommandType.StoredProcedure;//
            cmd.CommandText = "ReturnBook";//
            cmd.Parameters.Clear();//
            cmd.Parameters.Add("@bookid", SqlDbType.VarChar, 15).Value = bookid;//
            var returnVal = cmd.Parameters.Add("@returnVal", SqlDbType.Int);//定义存储过程输出参数output
            returnVal.Direction = ParameterDirection.Output;

            int num = DBHelper.ExecuteSQL(cmd);

            try
            {
                if (num > 0 || Convert.ToInt16(returnVal) == 1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool RenewBook( string  bookid)
       
        {
            cmd.CommandType = CommandType.StoredProcedure;//
            cmd.CommandText = "RenewBook";//
            cmd.Parameters.Clear();//
            cmd.Parameters.Add("@bookid", SqlDbType.VarChar, 15).Value = bookid;//
            var returnVal = cmd.Parameters.Add("@returnVal", SqlDbType.Int);//定义存储过程输出参数output
            returnVal.Direction = ParameterDirection.Output;

            int num = DBHelper.ExecuteSQL(cmd);

            try
            {
                if (num > 0 || Convert.ToInt16(returnVal) == 1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 用户信息管理
        /// </summary>
        /// <returns></returns>
        public DataSet GetClassInfo()
        {
            cmd.CommandText = "GetClassInfo";//指定存储过程名称
            cmd.Parameters.Clear();//清除存储过程参数值

            DataSet ds = DBHelper.GetDataSet(cmd);
            return ds;
        }
        public Classinfo FindClassInfo(int ClassName = 0, string ClassID = null)
        {
            Classinfo   obj = new Classinfo  ();
            if (ClassName ==0 )
                obj.classid = 0;
            else obj.classid = ClassName;
            if (ClassID == null)
                obj.className  = "";
            else obj.className  = ClassID;
            cmd.CommandText = "GetClassInfoByClass";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@ClassName", SqlDbType.Int  ).Value = obj.classid ;
            cmd.Parameters.Add("@ClassID", SqlDbType.VarChar, 50).Value = obj.className ;
            SqlDataReader reader = DBHelper.ExcuteReader(cmd);
            while (reader.Read())
            {
                Classinfo  o = GetCalssInfoByDataReader(reader);
                reader.Close();
                return o;
            }
            reader.Close();
            return null;
        }
        private static Classinfo  GetCalssInfoByDataReader(SqlDataReader reader)
        {
            int  ClassID = Convert .ToInt32  (reader["classId"].ToString().Trim());
            string ClassName = reader["className"].ToString().Trim();
            Classinfo  obj = new Classinfo (ClassID, ClassName);
            return obj;
        }
        /// <summary>
        /// 读者管理
        /// </summary>
        /// <param name="bo"></param>
        /// <returns></returns>
        public bool GetUserInfoByUserid(userinfo  bo)
        {
            cmd.CommandText = "GetUserbyUserID";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@UserID", SqlDbType.Char, 15).Value = bo.userId ;
            object obj = DBHelper.GerScalar(cmd);
            if (obj != null)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public bool EditClass(userinfo  bo, string operation)
        {
            if (operation == "I")
                cmd.CommandText = "InsertNewUser"; //当操作值为"I"，进行新增操作
            if (operation == "U")
                cmd.CommandText = "UpdateUser"; //当操作值为"U"，进行更新操作
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@UserID", SqlDbType.VarChar, 15).Value = bo.userId ;
            cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 200).Value = bo.userName;
            cmd.Parameters.Add("@Pass", SqlDbType.VarChar, 20).Value = bo.pwd ;
            cmd.Parameters.Add("@Email", SqlDbType.VarChar, 50).Value = bo.email ;
            cmd.Parameters.Add("@GEnder", SqlDbType.Bit).Value = bo.gender;
           
           
            cmd.Parameters.Add("@ClassID", SqlDbType.Int ).Value = bo.className  ;


            try
            {
                int num = DBHelper.ExecuteSQL(cmd);
                if (num > 0)
                {
                    return true;
                }
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataSet GetUserByUserIdOrClassname(string UserId, string classname)
        {
            string Classame = "";
            Classinfo  bo = new Classinfo ();
            bo.className  = classname;
            if (bo.className .Length > 0)
            {

                Classame = FindClassInfo(bo.classid , bo.className ).className  ;
            }
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetUserInfoBySearch";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@UserId", SqlDbType.NChar , 11).Value = UserId ;
            cmd.Parameters.Add("@Classid", SqlDbType.Int ).Value = classname ;

            DataSet ds = DBHelper.GetDataSet(cmd);
            return ds;
        }
    }
}

